import org.apache.commons.lang3.StringUtils;

public class Db39 {

  // 打印sql
  public static void main(String[] args) {
    Db39 db2 = new Db39();
    String sql = db2.getSql(null, null, "困境", null);
    System.out.println(sql);
  }

  /**
   * 获得要执行的SQL脚本
   *
   * @param
   * @return 需要执行的SQL脚本
   */
  //    @Override
  //    public String getSql(DbNode node, Map<String, NodeParam> binds){
  //        JSONObject in = getIn(binds);
  //        String type = in.getString("type");
  //        String name = in.getString("name");
  //        String etlb = in.getString("etlb");
  //        if(StringUtils.isNotEmpty(type)){
  //            switch (type){
  //                case "区":
  //                    return getSql4(etlb);
  //                case "街道":
  //                    return getSql3(name,etlb);
  //                case "村":
  //                    return getSql2(name,etlb);
  //                case "网格":
  //                    return getSql1(name,etlb);
  //            }
  //        }
  //        return getSql4(etlb);
  //    }

  private String getSql4(String etlb) {
    String sql =
        "select max(name) name ,max(code) code,sum(num) num from (\n"
            + "select if(xzqmc is null,'其他',xzqmc) name,max(xzqdm) code,count(*) num from ads_czc_dw_gdsgexx_czc where 1=1 \n"
            + getSearchSql("etlb", etlb)
            + " group by xzqmc\n"
            + "union all\n"
            + "select if(xzqmc is null,'其他',xzqmc) name,max(xzqdm) code,count(*) num from ads_dw_kjetxx_czc where 1=1 \n"
            + getSearchSql("etlx", etlb)
            + " group by xzqmc\n"
            + ")t group by name\n"
            + "order by field(name,'越秀区','海珠区','荔湾区','天河区','白云区','黄埔区','花都区','番禺区','南沙区','从化区','增城区','其他')\n";
    return sql;
  }

  private String getSql3(String name, String etlb) {
    String sql =
        "select max(name) name ,max(code) code,sum(num) num from (\n"
            + "select if(jzmc is null,'其他',jzmc) name,max(jzdm) code,count(*) num from ads_czc_dw_gdsgexx_czc where 1=1 \n"
            + getSearchSql("etlb", etlb)
            + " group by jzmc\n"
            + "union all\n"
            + "select if(jzmc is null,'其他',jzmc) name,max(jzdm) code,count(*) num from ads_dw_kjetxx_czc where 1=1 \n"
            + getSearchSql("etlx", etlb)
            + " group by jzmc\n"
            + ")t group by name\n"
            + "order by field(name,'越秀区','海珠区','荔湾区','天河区','白云区','黄埔区','花都区','番禺区','南沙区','从化区','增城区','其他')";
    return sql;
  }

  private String getSql2(String name, String etlb) {
    String sql =
        "select max(name) name ,max(code) code,sum(num) num from (\n"
            + "select if(czcmc is null,'其他',czcmc) name,max(czcdm) code,count(*) num from ads_czc_dw_gdsgexx_czc where 1=1 \n"
            + getSearchSql("etlb", etlb)
            + " group by czcmc\n"
            + "union all\n"
            + "select if(czcmc is null,'其他',czcmc) name,max(czcdm) code,count(*) num from ads_dw_kjetxx_czc where 1=1 \n"
            + getSearchSql("etlx", etlb)
            + " group by czcmc\n"
            + ")t group by name\n"
            + "order by field(name,'越秀区','海珠区','荔湾区','天河区','白云区','黄埔区','花都区','番禺区','南沙区','从化区','增城区','其他')";
    return sql;
  }

  private String getSql1(String name, String etlb) {
    String sql =
        "select max(name) name ,max(code) code,sum(num) num from (\n"
            + "select if(wgmc is null,'其他',wgmc) name,max(wgdm) code,count(*) num from ads_czc_dw_gdsgexx_czc where 1=1 \n"
            + "where 1=1   \n"
            + getSearchSql("czcmc", name)
            + getSearchSql("etlb", etlb)
            + " group by wgmc\n"
            + "union all\n"
            + "select if(wgmc is null,'其他',wgmc) name,max(wgdm) code,count(*) num from ads_dw_kjetxx_czc where 1=1 \n"
            + "where 1=1   \n"
            + getSearchSql("czcmc", name)
            + getSearchSql("etlx", etlb)
            + " group by wgmc\n"
            + ")t group by name\n"
            + "order by field(name,'越秀区','海珠区','荔湾区','天河区','白云区','黄埔区','花都区','番禺区','南沙区','从化区','增城区','其他')";
    return sql;
  }

  public String getSql(String czcdm, String type, String etlb, String name) {
    if (StringUtils.isNotEmpty(type)) {
      switch (type) {
        case "区":
          return getSql4(etlb);
        case "街道":
          return getSql3(name, etlb);
        case "村":
          return getSql2(name, etlb);
        case "网格":
          return getSql1(name, etlb);
      }
    }
    return getSql4(etlb);
  }

  // 获取前端传过来的入参
  //    public static JSONObject getIn(Map<String, NodeParam> params){
  //        NodeParam input = params.get("input");
  //        JSONObject json = (JSONObject) JSON.toJSON(input.getIn());
  //        return json;
  //    }

  public String getSearchSql(String column, String value) {
    String sql = " and 1=1 ";
    if (StringUtils.isNotBlank(value)) {
      if ("其他".equals(value)) {
        sql = " and " + column + " is null ";
      } else {
        sql = " and " + column + "  =  " + "'" + value + "'";
      }
    }
    return sql;
  }
}
